package com.budwk.app.sqfw.services.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.text.StrFormatter;
import cn.hutool.core.util.IdcardUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.extra.pinyin.PinyinUtil;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.budwk.app.common.component.DictComponent;
import com.budwk.app.common.config.AppException;
import com.budwk.app.sqfw.component.CommonComponent;
import com.budwk.app.sqfw.component.FromSummaryComponent;
import com.budwk.app.sqfw.constants.CommonConstants;
import com.budwk.app.sqfw.constants.DictConstants;
import com.budwk.app.sqfw.dto.HouseSurveyFormExcelParamDTO;
import com.budwk.app.sqfw.enums.*;
import com.budwk.app.sqfw.models.Sqfw_cenSusPaper;
import com.budwk.app.sqfw.models.Sqfw_formSummary;
import com.budwk.app.sqfw.models.Sqfw_servicerecord;
import com.budwk.app.sqfw.services.SqfwCenSusPaperService;
import com.budwk.app.sqfw.services.SqfwFormSummaryService;
import com.budwk.app.sqfw.services.SqfwPeopleInfoForAdminService;
import com.budwk.app.sqfw.services.SqfwServicerecordService;
import com.budwk.app.sqfw.vo.SqfwCenSusPaperVo;
import com.budwk.app.sys.models.Sys_user;
import com.budwk.app.sys.services.SysDictService;
import com.budwk.app.sys.services.SysUserService;
import com.budwk.app.sys.vo.SysDictVO;
import com.budwk.app.util.AuthorityUtil;
import com.budwk.app.util.PoiTlUtil;
import com.budwk.app.util.StrUtil;
import com.budwk.app.util.poiUtils.CommonFormOfficeUtils;
import com.budwk.app.util.poiUtils.HouseSurveyFormOfficeUtils;
import com.budwk.app.util.poiUtils.excelPictureConfigBO.ExcelFileCreateParamBO;
import com.budwk.app.util.poiUtils.nutzBeanUtils.NutzIocUtils;
import com.budwk.starter.common.result.Result;
import com.budwk.starter.common.result.ResultCode;
import com.budwk.starter.database.service.BaseServiceImpl;
import com.budwk.starter.security.utils.SecurityUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.nutz.aop.interceptor.ioc.TransAop;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.nutz.ioc.aop.Aop;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.json.Json;
import org.nutz.lang.Strings;
import org.nutz.lang.util.NutMap;
import org.nutz.mvc.upload.TempFile;

import java.sql.Connection;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.stream.Collectors;

import static com.budwk.app.util.poiUtils.HouseSurveyFormOfficeUtils.nineTypesOfPersonnelPatrolExcel;
import static com.budwk.app.util.poiUtils.HouseSurveyFormOfficeUtils.sixCategoriesOfPersonnel;

/**
 * @program: wk-mini
 * @description: 人员信息服务实现
 * @author: LJT
 * @create: 2023-11-07 14:20
 **/
@Slf4j
@IocBean(args = {"refer:dao"})
public class SqfwPeopleInfoForAdminServiceImpl extends BaseServiceImpl<Sqfw_cenSusPaper> implements SqfwPeopleInfoForAdminService {
    public SqfwPeopleInfoForAdminServiceImpl(Dao dao) {
        super(dao);
    }

    private static final int PEOPLE_INFO = 1;// 人员信息表
    private static final int STATE_OWNED_INFO = 2;// 国有企业退休人员信息台账

    private static final int NINE_TYPES_OF_PERSONNEL_PATROL = 4;// 九类人员巡查台账

    private static final int SIX_CATEGORIES_OF_PERSONNEL = 5;// 老、弱、病、残、酒、疯”六类人员台账

    @Inject
    private SysUserService sysUserService;
    @Inject
    private SqfwCenSusPaperService sqfwCenSusPaperService;
    @Inject
    private SqfwFormSummaryService sqfwFormSummaryService;
    @Inject
    private SqfwServicerecordService sqfwServicerecordService;
    @Inject
    private SysDictService sysDictService;

    @Inject("taskQueryExecutor")
    private ThreadPoolExecutor taskQueryExecutor;

    /**
     * 获取pdfExcel下载sumId列表
     *
     * @param bean
     * @param queryAll
     */
    @Override
    public List<String> getPdfExcelFileDownSumIdList(Sqfw_cenSusPaper bean, String queryAll, String queryTime) {
        AuthorityUtil.checkCenSusPaperAreaPermission(bean);
        String sql = buildConditionForQueryToString(bean, queryAll, queryTime);
        return getPdfExcelFileDownSumIdList(sql);
    }


    /**
     * 生成并上传Excel
     *
     * @param ids
     * @param fileIndex
     * @return
     */
    @Override
    public String createExcelAndUpload(List<String> ids, Integer fileIndex, String isCheckData, String startTime, String endTime) {
        if (ids.size() <= 0) {
            return null;
        }

        // 九类人员巡查台账和六类人员
        if (NINE_TYPES_OF_PERSONNEL_PATROL == fileIndex || SIX_CATEGORIES_OF_PERSONNEL == fileIndex) {
            return generatePeopleInfoExcelForAppByCond(null, null, fileIndex, isCheckData, startTime, endTime, ids);
//            String sql = "select sc.*, sfs.fill_date,sfs.a11,sfs.a12 from shequ.sqfw_censuspaper sc " +
//                    "left join shequ.sqfw_form_summary sfs on sc.community = sfs.community and sc.name = sfs.a2 ";
            /*
            String sql = "select ta.*,tb.fill_date,tb.a11,tb.a12 from shequ.sqfw_censuspaper ta \n" +
                    "left join (select fill_date,a11,a12,id,  (extracted_name::jsonb)->>'name' AS name, \n" +
                    "(extracted_name::jsonb)->>'phone' AS phone,      (extracted_name::jsonb)->>'cardId' AS cardId, \n" +
                    "(extracted_name::jsonb)->>'userNotes' AS userNotes,       \n" +
                    "(extracted_name::jsonb)->>'specialMark' AS specialMark,\n" +
                    "coalesce((extracted_name::jsonb)->>'phone') p,coalesce((extracted_name::jsonb)->>'specialMark') s  \n" +
                    "FROM  \n" +
                    "(SELECT fill_date,a11,a12,id,jsonb_array_elements_text(param_data) AS extracted_name FROM shequ.sqfw_form_summary \n" +
                    "WHERE type = '入户调查' and jsonb_typeof(param_data) = 'array'  ) tt) tb on \n" +
                    "ta.name = tb.name and coalesce(ta.phone,'0')=coalesce(tb.p,'0') and coalesce(ta.special_mark,'0')=coalesce(tb.s,'0')";
            Cnd cnd = Cnd.NEW();
            cnd.and("ta.id", "in", ids);
            cnd.orderBy("tb.fill_date", "desc");
            sql += cnd.toString();
            List<NutMap> nutMapList = sqfwCenSusPaperService.listMap(Sqls.create(sql));

            List<Sqfw_cenSusPaper> cenSusPaperList = JSONUtil.toList(JSONUtil.toJsonStr(nutMapList), Sqfw_cenSusPaper.class);
            if (cenSusPaperList.size() == 0) {
                return null;
            }
            //zhangp 2024-01-11 提出共用
            return createNinAndSixExcel(cenSusPaperList,fileIndex,isCheckData);
            */
            /*
            Map<String, Sqfw_cenSusPaper> map = new HashMap<>();
            for (Sqfw_cenSusPaper susPaper : cenSusPaperList) {
                String date = DateUtil.format(susPaper.getFillDate(), "yyyy-MM-dd");
                susPaper.setFillDateStr(date);
                if (map.containsKey(susPaper.getId())) {
                    Sqfw_cenSusPaper paper = map.get(susPaper.getId());
                    if (StringUtils.isNotBlank(susPaper.getA11())) {
                        paper.setA11(StringUtils.isBlank(paper.getA11()) ? susPaper.getA11() : paper.getA11() + "," + susPaper.getA11());
                    }
                    if (StringUtils.isNotBlank(susPaper.getA12())) {
                        paper.setA12(StringUtils.isBlank(paper.getA12()) ? susPaper.getA12() : paper.getA12() + "," + susPaper.getA12());
                    }
                    paper.setFillDateStr(paper.getFillDateStr() + "，" + date);
                } else {
                    map.put(susPaper.getId(), susPaper);
                }
            }

            List<Sqfw_cenSusPaper> cpaperList = new ArrayList<>();
            for (String key : map.keySet()) {
                cpaperList.add(map.get(key));
            }

            if (NINE_TYPES_OF_PERSONNEL_PATROL == fileIndex) {
                return nineTypesOfPersonnelPatrolExcel(cpaperList, isCheckData);
            }
            if (SIX_CATEGORIES_OF_PERSONNEL == fileIndex) {
                return sixCategoriesOfPersonnel(cpaperList, isCheckData);
            }
            */
        }

        Cnd cnd = Cnd.NEW();
        cnd.and("id", "in", ids);
        if (STATE_OWNED_INFO == fileIndex) {
            cnd.and("special_mark", "like", "%国有企业退休%");
        }

        List<Sqfw_cenSusPaper> cenSusPaperList = sqfwCenSusPaperService.query(cnd);
        if (cenSusPaperList.size() <= 0) {
            return null;
        }

        // 生成文档
        if (PEOPLE_INFO == fileIndex) {
            // 人员信息表
            return createPeopleInfoExcelAndUpload(cenSusPaperList, isCheckData);
        }
        if (STATE_OWNED_INFO == fileIndex) {
            // 国有退休人员信息台账
            return createStateOwnedExcelAndUpload(cenSusPaperList, isCheckData);
        }

        // Excel重点人员分类数据统计表.xls
        if (3 == fileIndex) {
            return houseSurveySixTypePeopleStatisticExport(cenSusPaperList, isCheckData);
        }

        return null;
    }

    private String createNinAndSixExcel(List<Sqfw_cenSusPaper> cpaperList, Integer fileIndex, String isCheckData) {

        /*for (Sqfw_cenSusPaper susPaper : cpaperList) {
            String date = DateUtil.format(susPaper.getUpdateDate(), "yyyy-MM-dd");
            susPaper.setFillDateStr(date);
        }*/

        if (NINE_TYPES_OF_PERSONNEL_PATROL == fileIndex) {
            return nineTypesOfPersonnelPatrolExcel(cpaperList, isCheckData);
        }
        if (SIX_CATEGORIES_OF_PERSONNEL == fileIndex) {
            return sixCategoriesOfPersonnel(cpaperList, isCheckData);
        }
        return null;
    }

    private String houseSurveySixTypePeopleStatisticExport(List<Sqfw_cenSusPaper> cenSusPaperList, String isCheckData) {
        // 1 检查是否已导出过
        String ids = cenSusPaperList.stream().map(Sqfw_cenSusPaper::getId).collect(Collectors.joining(""));
        String title = String.format("%s%s", "下载Excel", HouseSurveyEnum.SIX_KEYNOTE_TABLE_STATIS.getName());
        // 2 构建数据
        List<Map<String, Object>> dataList = Lists.newArrayList();
        Map<String, Object> excelMap = Maps.newHashMap();
        // 查询用户信息
        SysUserService sysUserService = (SysUserService) NutzIocUtils.getBeanByType(SysUserService.class);
        Sys_user user = sysUserService.fetch(SecurityUtil.getUserId());
        excelMap.put("county", user.getCounty());
        // 按照 街道（street）+社区（community）去重后得到数据
        ArrayList<Sqfw_cenSusPaper> collect = cenSusPaperList.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() ->
                new TreeSet<>(Comparator.comparing(o -> o.getCounty() + ":" + o.getCommunity()))), ArrayList::new));
        int num = 1;
        // 批量查询
        List<String> sqfwIds = cenSusPaperList.stream().map(Sqfw_cenSusPaper::getSqfwId).distinct().collect(Collectors.toList());
        List<Sqfw_servicerecord> servicerecords = sqfwServicerecordService.query(Cnd.where(Sqfw_servicerecord::getId, "in", sqfwIds));
        boolean houseCountFlag = false;//统计六类住户人员状态标志
        for (Sqfw_cenSusPaper cenSusPaper : collect) {
            List<Sqfw_cenSusPaper> exportData = getSixTypePeopleStatisticExportData(cenSusPaper, cenSusPaperList);
            // 按照街道和社区取出数据
            HouseSurveyFormExcelParamDTO param = new HouseSurveyFormExcelParamDTO();
            String sqfwId = exportData.get(0).getSqfwId();
            if (StringUtils.isNotEmpty(sqfwId)) {
                Sqfw_servicerecord serviceRecord = servicerecords.stream().filter(s -> sqfwId.equals(s.getId())).findFirst().get();
                param.setCounty(serviceRecord.getCounty());
            }
            // 构建内容体
            Map<String, Object> dataMap = HouseSurveyFormOfficeUtils.buildCenSusPaperDataMap(exportData, num, param);
            // 统计户数
            long houseCount = HouseSurveyFormOfficeUtils.sumSurveySixTypePeopleHouse(exportData);
            dataMap.put("houseCount", houseCount);
            dataList.add(dataMap);
            num++;
            //zhangp 2023-12-12 检测数据时使用,只要存在一条数据就返回
            if (StringUtils.isNotBlank(isCheckData) && "isCheckData".equals(isCheckData)) {
                if (houseCount > 0) {
                    houseCountFlag = true;
                    return String.valueOf(houseCountFlag);
                }
            }
        }
        //zhangp 2023-12-18 模板下载时处理
        if (StringUtils.isNotBlank(isCheckData) && "isTemplate".equals(isCheckData)) {
            // 按照街道和社区取出数据
            HouseSurveyFormExcelParamDTO param = new HouseSurveyFormExcelParamDTO();
            param.setCounty(user.getCounty());
            param.setCompleteUnitName(user.getUnitName());
            // 构建内容体
            Map<String, Object> dataMap = HouseSurveyFormOfficeUtils.buildCenSusPaperDataMap(Lists.newArrayList(), num, param);
            dataMap.put("houseCount", 0);
            dataList.add(dataMap);
        }
        excelMap.put("list", dataList);

        //zhangp 2023-12-12 检测数据时使用
        if (StringUtils.isNotBlank(isCheckData) && "isCheckData".equals(isCheckData)) {
            return String.valueOf(houseCountFlag);
        }
        //String fileName = "Excel六类重点人员分类数据统计表";
        //zhangp 2023-12-12 文件名统一
        String fileName = DownLoadFileNameEnum.SIX_KEYNOTE_TABLE_STATIS.getName();
        String url = FromSummaryComponent.generateExcelLedger(excelMap, "houseSurveySixPeopleStatistic.xlsx",
                fileName, SupportBusinessTypeEnum.HOUSE_SURVEY_FORM.getName());
        log.info("houseSurveySixTypePeopleStatisticExport url: {}", url);
        // 3 入导出记录数据库
        CommonComponent.insertDownRecordLog(null, JSONObject.toJSONString(Result.data(url)), title, url, ids);
        return url;
    }

    private List<Sqfw_cenSusPaper> getSixTypePeopleStatisticExportData(Sqfw_cenSusPaper cenSusPaper, List<Sqfw_cenSusPaper> cenSusPaperList) {
        // 1 社区 街道都是空
        if (StringUtils.isEmpty(cenSusPaper.getCounty()) && StringUtils.isEmpty(cenSusPaper.getCommunity())) {
            return cenSusPaperList.stream().filter(e -> StringUtils.isEmpty(e.getCounty()) && StringUtils.isEmpty(e.getCommunity())).collect(Collectors.toList());
        }
        // 2 街道不为空 社区为空
        if (StringUtils.isNotEmpty(cenSusPaper.getCounty()) && StringUtils.isEmpty(cenSusPaper.getCommunity())) {
            return cenSusPaperList.stream().filter(e -> e.getCounty().equals(cenSusPaper.getCounty()) && StringUtils.isEmpty(e.getCommunity())).collect(Collectors.toList());
        }
        // 3 街道为空 社区不为空
        if (StringUtils.isEmpty(cenSusPaper.getCounty()) && StringUtils.isNotEmpty(cenSusPaper.getCommunity())) {
            return cenSusPaperList.stream().filter(e -> e.getCommunity().equals(cenSusPaper.getCommunity()) && StringUtils.isEmpty(e.getCounty())).collect(Collectors.toList());
        }
        // 4 社区 街道都不为空
        return cenSusPaperList.stream().filter(e -> cenSusPaper.getCounty().equals(e.getCounty()) && cenSusPaper.getCommunity().equals(e.getCommunity())).collect(Collectors.toList());
    }

    /**
     * 生成人员信息表并上传
     *
     * @param peopleInfoList
     * @param isCheckData
     * @return
     */

    private String createPeopleInfoExcelAndUpload(List<Sqfw_cenSusPaper> peopleInfoList, String isCheckData) {
        //生成人员信息excel表
        try {
            Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
            Map<String, Object> peopleInfoParamMap = Maps.newHashMap();
            peopleInfoParamMap.put("crateBy", sysUser.getUsername());
            peopleInfoParamMap.put("crateDate", DateUtil.format(new Date(), "yyyyMMdd"));

            List<Map<String, Object>> dataList = Lists.newArrayList();

            int visAndInsNumIndex = 0;
            for (Sqfw_cenSusPaper peopleInfo : peopleInfoList) {
                visAndInsNumIndex++;
                Map<String, Object> rowDataMap = Maps.newHashMap();
                rowDataMap.put("id", visAndInsNumIndex);
                rowDataMap.put("name", peopleInfo.getName());
                rowDataMap.put("phone", peopleInfo.getPhone());
                if (Strings.isNotBlank(peopleInfo.getCardId())) {
                    rowDataMap.put("number", "有");
                } else {
                    rowDataMap.put("number", "无");
                }
                rowDataMap.put("grid", peopleInfo.getGrid());
                rowDataMap.put("special", peopleInfo.getSpecialMark());
                rowDataMap.put("community", peopleInfo.getCommunity());
                rowDataMap.put("courtyard", peopleInfo.getCourtyard());
                rowDataMap.put("housenumber", peopleInfo.getHouseNumber());
                dataList.add(rowDataMap);
                //zhangp 2023-12-12 检测数据时使用,只要存在一条数据就返回
                if (StringUtils.isNotBlank(isCheckData) && "isCheckData".equals(isCheckData)) {
                    return "true";
                }
            }
            //zhangp 2023-12-12 检测数据时使用
            if (StringUtils.isNotBlank(isCheckData) && "isCheckData".equals(isCheckData)) {
                if (dataList.isEmpty()) {
                    return "false";
                }
            }
            String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("peopleInfo.xlsx");
            //String fileName = "人员信息表" + DateUtil.format(new Date(), "yyyyMMddHHmmss");
            //zhangp 2023-12-11 文件名统一
            String fileName = DownLoadFileNameEnum.HOUSEHOLD_PERSONNEL_INFO.getName() + DateUtil.format(new Date(), "yyyyMMddHHmmss");
            ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
            excelFileCreateParam.setTemplatePath(templatePath);
            excelFileCreateParam.setSheetParam(peopleInfoParamMap);
            excelFileCreateParam.setDataList(dataList);
            excelFileCreateParam.setClazz(null);
            excelFileCreateParam.setFileName(fileName);
            return CommonFormOfficeUtils.createExcelAndUpload(excelFileCreateParam);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(StrFormatter.format("生成" + DaYingLuExcelType.THREE_DETAILED.getName() + "表错误"));
        }
        return null;
    }

    /**
     * 生成国有退休人员信息表并上传
     *
     * @param peopleInfoList
     * @return
     */

    private String createStateOwnedExcelAndUpload(List<Sqfw_cenSusPaper> peopleInfoList, String isCheckData) {
        //生成人员信息excel表
        try {
            Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
            List<Map<String, Object>> dataList = Lists.newArrayList();

            int visAndInsNumIndex = 0;
            for (Sqfw_cenSusPaper peopleInfo : peopleInfoList) {
                visAndInsNumIndex++;
                Map<String, Object> rowDataMap = Maps.newHashMap();
                rowDataMap.put("id", visAndInsNumIndex);
                rowDataMap.put("name", peopleInfo.getName());
                rowDataMap.put("card_id", peopleInfo.getCardId());
                rowDataMap.put("create_user_name", peopleInfo.getCreateUserName());
                rowDataMap.put("create_user_phone", peopleInfo.getCreateUserPhone());
                rowDataMap.put("community", peopleInfo.getCommunity());
                rowDataMap.put("grid", peopleInfo.getGrid());
                rowDataMap.put("registered", peopleInfo.getRegistered());
                rowDataMap.put("detail_address", peopleInfo.getDetailAddress());
                rowDataMap.put("phone", peopleInfo.getPhone());
                rowDataMap.put("in_region", peopleInfo.getInRegion());
                rowDataMap.put("in_municipal", peopleInfo.getInMunicipal());
                rowDataMap.put("health_status", peopleInfo.getHealthStatus());
                rowDataMap.put("intend", peopleInfo.getIntend());
                rowDataMap.put("strengths", peopleInfo.getStrengths());
                rowDataMap.put("special_cir", peopleInfo.getSpecialCir());
                rowDataMap.put("enterprise", peopleInfo.getEnterprise());
                rowDataMap.put("participate_times", peopleInfo.getParticipateTimes());
                rowDataMap.put("bonus", peopleInfo.getBonus());
                dataList.add(rowDataMap);
                //zhangp 2023-12-12 检测数据时使用,只要存在一条数据就返回
                if (StringUtils.isNotBlank(isCheckData) && "isCheckData".equals(isCheckData)) {
                    return "true";
                }
            }
            //zhangp 2023-12-12 检测数据时使用
            if (StringUtils.isNotBlank(isCheckData) && "isCheckData".equals(isCheckData)) {
                if (dataList.isEmpty()) {
                    return "false";
                }
            }
            String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("stateOwnedRetireesBook.xlsx");
            //String fileName = "国有退休人员台账" + DateUtil.format(new Date(), "yyyyMMddHHmmss");
            //zhangp 2023-12-11 文件名统一
            String fileName = DownLoadFileNameEnum.RETIRE_PERSONNEL.getName() + DateUtil.format(new Date(), "yyyyMMddHHmmss");
            ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
            excelFileCreateParam.setTemplatePath(templatePath);
            excelFileCreateParam.setSheetParam(Maps.newHashMap());
            excelFileCreateParam.setDataList(dataList);
            excelFileCreateParam.setClazz(null);
            excelFileCreateParam.setFileName(fileName);
            return CommonFormOfficeUtils.createExcelAndUpload(excelFileCreateParam);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(StrFormatter.format("生成" + DaYingLuExcelType.THREE_DETAILED.getName() + "表错误"));
        }
        return null;
    }

    @Override
    @Aop(TransAop.READ_COMMITTED)
    public Result<?> importHouseholdData(TempFile file) {
        // 1 获取表格的单元格
        Sheet sheet = PoiTlUtil.getBookSheet(file);
        // 2 获取导入的数据
        List<Sqfw_cenSusPaper> importDatas = getCenSusPaperInportData(sheet);
        if (importDatas.isEmpty()) {
            return Result.success();
        }
        // 解析表格数据(区分重复数据和不重复数据)
        Set<String> reIdcards = new HashSet<>(); // 用来记录重复的身份证号
        List<String> list1 = new ArrayList<>(); // 用来记录身份证号
        for (Sqfw_cenSusPaper importData : importDatas) {
            if (StringUtils.isNotEmpty(importData.getCardId())) {
                long total = list1.stream().filter(e -> importData.getCardId().equals(e)).count();
                if (total > 0) {
                    reIdcards.add(importData.getCardId());
                    continue;
                }
                list1.add(importData.getCardId());
            }
        }
        // 删除重复元素
        importDatas.removeIf(e -> reIdcards.contains(e.getCardId()));
        List<String> idcards = importDatas.stream().map(Sqfw_cenSusPaper::getCardId).filter(StringUtils::isNotEmpty).distinct().collect(Collectors.toList());
        if (!idcards.isEmpty()) {
            // 按照身份证号去查询存在的数据 存在先删除
            List<Sqfw_cenSusPaper> queryList = this.query(Cnd.where(Sqfw_cenSusPaper::getCardId, "in", idcards));
            if (!queryList.isEmpty()) {
                List<String> ids = queryList.stream().map(Sqfw_cenSusPaper::getId).collect(Collectors.toList());
                this.delete(ids);
            }
        }
        // 3 新增数据
        this.fastInsert(importDatas);
        if (reIdcards.size() > 0) {
            return Result.error("入库成功数据：" + importDatas.size() + "条" + ",存在重复身份证号为：【" + reIdcards + "】请修改后重新导入");
        }
        return Result.success();
    }

    @Override
    public void updateCenSusPaper(SqfwCenSusPaperVo paperVo) {
        if (StringUtils.isEmpty(paperVo.getId())) {
            throw new AppException("请传递操作id");
        }
        Sqfw_cenSusPaper cenSusPaper = fetch(paperVo.getId());
        if (cenSusPaper == null) {
            throw new AppException(ResultCode.NULL_DATA_ERROR.getMsg());
        }
        // 复制对象
        Sqfw_cenSusPaper cenSusPaper1 = new Sqfw_cenSusPaper();
        BeanUtil.copyProperties(paperVo, cenSusPaper1);
        cenSusPaper1.setUpdateBy(SecurityUtil.getUserId()).setUpdateDate(new Date());
        this.updateIgnoreNull(cenSusPaper1);
    }

    private List<Sqfw_cenSusPaper> getCenSusPaperInportData(Sheet sheet) {
        // 获取所有导入的数据
        List<JSONObject> importContents = PoiTlUtil.getImportContent(sheet, 2);
        if (importContents.isEmpty()) {
            return Collections.emptyList();
        }
        List<Sqfw_cenSusPaper> results = new ArrayList<>();
        // 获取数据字典项
        List<SysDictVO> sysDicts = DictComponent.getSpecialPersonnelTypes();
        for (JSONObject json : importContents) {
            json.put("createDate", DateUtil.parse(CommonConstants.WASTE_CREATE_TIME));
            json.put("updateDate", new Date());
            Sqfw_cenSusPaper cenSusPaper = JSONObject.toJavaObject(json, Sqfw_cenSusPaper.class);
            // 处理空字符
            StrUtil.handleStrTrim(cenSusPaper);
            cenSusPaper.setCreateBy(SecurityUtil.getUserId())
                    .setRemark(DateUtil.format(new Date(), DatePattern.PURE_DATETIME_PATTERN));
            boolean validCard = IdcardUtil.isValidCard(cenSusPaper.getCardId());
            log.info("importHouseholdData getCenSusPaperInportData idcard: {}", cenSusPaper.getCardId());
            if (validCard) {
                int ageByIdCard = IdcardUtil.getAgeByIdCard(cenSusPaper.getCardId());
                int genderByIdCard = IdcardUtil.getGenderByIdCard(cenSusPaper.getCardId());
                DateTime birthDate = IdcardUtil.getBirthDate(cenSusPaper.getCardId());
                cenSusPaper.setGender(genderByIdCard == 0 ? "女" : "男")
                        .setAge(ageByIdCard)
                        .setBirthday(birthDate);
            }
            // 解析特殊人员类型
            cenSusPaper.setSpecialMarkIds(CommonComponent.getSpecialMarkIds(cenSusPaper.getSpecialMark(), sysDicts));
            results.add(cenSusPaper);
        }
        log.info("importHouseholdData getCenSusPaperInportData info: {}", JSONObject.toJSONString(results));
        return results;
    }

    /**
     * 获取巡查记录的查询条件 Cnd类型
     *
     * @param bean
     * @param pageOrderName
     * @param pageOrderBy
     */
    public Cnd buildConditionForQueryToCnd(Sqfw_cenSusPaper bean, String queryAll, String queryTime, String pageOrderName, String pageOrderBy) {
        Cnd cnd = Cnd.NEW();
        AuthorityUtil.checkCenSusPaperAreaPermission(bean);
        cnd.orderBy("update_date", "desc");
        if (Strings.isNotBlank(queryAll)) {
            SqlExpressionGroup e1 = Cnd.exps("name", "like", "%" + queryAll + "%");
            SqlExpressionGroup e2 = Cnd.exps("phone", "like", "%" + queryAll + "%");
            SqlExpressionGroup e3 = Cnd.exps("grid", "like", "%" + queryAll + "%");
            SqlExpressionGroup e4 = Cnd.exps("special_mark", "like", "%" + queryAll + "%");
            SqlExpressionGroup e5 = Cnd.exps("courtyard", "like", "%" + queryAll + "%");
            SqlExpressionGroup e6 = Cnd.exps("house_number", "like", "%" + queryAll + "%");
            SqlExpressionGroup e7 = Cnd.exps("community", "like", "%" + queryAll + "%");
            SqlExpressionGroup e8 = Cnd.exps("county", "like", "%" + queryAll + "%");
            SqlExpressionGroup e9 = Cnd.exps("registered", "like", "%" + queryAll + "%");
            cnd.and(e1.or(e2).or(e3).or(e4).or(e5).or(e6).or(e7).or(e8).or(e9));
        }
        if (Strings.isNotBlank(bean.getCardId())) {
            if ("0".equals(bean.getCardId())) {
                SqlExpressionGroup e1 = Cnd.exps("card_id", "=", "");
                SqlExpressionGroup e2 = Cnd.exps("card_id", "is", null);
                cnd.and(e1.or(e2));
            } else if ("1".equals(bean.getCardId())) {
                SqlExpressionGroup e1 = Cnd.exps("card_id", "!=", "");
                SqlExpressionGroup e2 = Cnd.exps("card_id", "is not", null);
                cnd.and(e1.or(e2));
            }
        }
        // 姓名
        if (Strings.isNotBlank(bean.getName())) {
            cnd.and("name", "like", "%" + bean.getName() + "%");
        }
        // 电话
        if (Strings.isNotBlank(bean.getPhone())) {
            cnd.and("phone", "like", "%" + bean.getPhone() + "%");
        }
        // 网格
        if (Strings.isNotBlank(bean.getGrid())) {
            cnd.and("grid", "like", "%" + bean.getGrid() + "%");
        }
        // 特殊标记
        if (Strings.isNotBlank(bean.getSpecialMark())) {
            List<SysDictVO> sysDicts = sysDictService.listDictByCode(DictConstants.SPECIAL_PERSONNEL_TYPES);
            // 获取数据字典项
            String[] specialMarks = bean.getSpecialMark().split(",");
            SqlExpressionGroup expressionGroup = new SqlExpressionGroup();
            for (String mark : specialMarks) {
                String finalMark = mark.equals("正常") ? "否" : mark;
                Optional<SysDictVO> optional = sysDicts.stream().filter(e -> e.getName().equals(finalMark)).findFirst();
                if (optional.isPresent()) {
                    SqlExpressionGroup e = Cnd.exps(Sqfw_cenSusPaper::getSpecialMarkIds, "like", "%" + optional.get().getId() + "%");
                    expressionGroup.or(e);
                }
            }
            cnd.and(expressionGroup);
        }
        // 院落名称
        if (Strings.isNotBlank(bean.getCourtyard())) {
            cnd.and("courtyard", "like", "%" + bean.getCourtyard() + "%");
        }
        // 门牌号
        if (Strings.isNotBlank(bean.getHouseNumber())) {
            cnd.and("house_number", "like", "%" + bean.getHouseNumber() + "%");
        }
        // 社区、辖区
        if (Strings.isNotBlank(bean.getCommunity())) {
            cnd.and("community", "like", "%" + bean.getCommunity() + "%");
        }
        // 省
        if (Strings.isNotBlank(bean.getProvince())) {
            cnd.and(Sqfw_cenSusPaper::getProvince, "like", "%" + bean.getProvince() + "%");
        }
        // 市
        if (Strings.isNotBlank(bean.getCity())) {
            cnd.and(Sqfw_cenSusPaper::getCity, "like", "%" + bean.getCity() + "%");
        }
        // 区县
        if (Strings.isNotBlank(bean.getDistrict())) {
            cnd.and(Sqfw_cenSusPaper::getDistrict, "like", "%" + bean.getDistrict() + "%");
        }
        // 街道
        if (Strings.isNotBlank(bean.getCounty())) {
            cnd.and(Sqfw_cenSusPaper::getCounty, "like", "%" + bean.getCounty() + "%");
        }
        // 楼栋
        if (Strings.isNotBlank(bean.getBuilding())) {
            cnd.and(Sqfw_cenSusPaper::getBuilding, "like", "%" + bean.getBuilding() + "%");
        }
        // 楼栋
        if (Strings.isNotBlank(bean.getSqfwSummaryId())) {
            cnd.and(Sqfw_cenSusPaper::getSqfwSummaryId, "=", bean.getSqfwSummaryId());
        }
        // 时间
        if (Strings.isNotBlank(queryTime)) {
            String time[] = queryTime.split(",");
            cnd.and("update_date", "between", new Object[]{new Date(Long.valueOf(time[0])), new Date(Long.valueOf(time[1]))});
        }
        // 居住状态
        if (Strings.isNotBlank(bean.getLatestStatus())) {
            // "0"表示在住对应表字段为空或者null的情况
            if ("0".equals(bean.getLatestStatus())) {
                SqlExpressionGroup e1 = Cnd.exps(Sqfw_cenSusPaper::getLatestStatus, "=", "");
                SqlExpressionGroup e2 = Cnd.exps(Sqfw_cenSusPaper::getLatestStatus, "is", null);
                cnd.and(e1.or(e2));
            } else {
                cnd.and(Sqfw_cenSusPaper::getLatestStatus, "=", bean.getLatestStatus());
            }
        }
        return cnd;
    }

    /**
     * 获取巡查记录的查询条件 String类型
     *
     * @param bean
     * @return
     */
    private String buildConditionForQueryToString(Sqfw_cenSusPaper bean, String queryAll, String queryTime) {
        StringBuffer sb = new StringBuffer(" select * from shequ.\"sqfw_censuspaper\" where 1 = 1 ");
        if (Strings.isNotBlank(queryAll)) {
            sb.append(" and ( name like '%").append(queryAll).append("%'")
                    .append(" OR phone like '%").append(queryAll).append("%'")
                    .append(" OR grid like '%").append(queryAll).append("%'")
                    .append(" OR special_mark like '%").append(queryAll).append("%'")
                    .append(" OR courtyard like '%").append(queryAll).append("%'")
                    .append(" OR house_number like '%").append(queryAll).append("%'")
                    .append(" OR community like '%").append(queryAll).append("%'")
                    .append(" OR county like '%").append(queryAll).append("%'");
            sb.append(")");
        }
        if (Strings.isNotBlank(bean.getCardId())) {
            if ("0".equals(bean.getCardId())) {
                sb.append(" and card_id = ''");
            } else if ("1".equals(bean.getCardId())) {
                sb.append(" and card_id != ''");
            }
        }
        if (Strings.isNotBlank(bean.getName())) {
            sb.append(" and name like '%").append(bean.getName()).append("%'");
        }
        if (Strings.isNotBlank(bean.getPhone())) {
            sb.append(" and phone like '%").append(bean.getPhone()).append("%'");
        }
        if (Strings.isNotBlank(bean.getGrid())) {
            sb.append(" and grid like '%").append(bean.getGrid()).append("%'");
        }
        if (Strings.isNotBlank(bean.getSpecialMark())) {
            String specialMark = bean.getSpecialMark();
            String specialMarks[] = specialMark.split(",");
            sb.append(" and (");
            for (int i = 0; i < specialMarks.length; i++) {
                sb.append(" special_mark like '%").append(specialMarks[i]).append("%'");
                if (i < specialMarks.length - 1) {
                    sb.append(" or");
                }
            }
            sb.append(")");
        }
        if (Strings.isNotBlank(bean.getCourtyard())) {
            sb.append(" and courtyard like '%").append(bean.getCourtyard()).append("%'");
        }
        if (Strings.isNotBlank(bean.getHouseNumber())) {
            sb.append(" and house_number like '%").append(bean.getHouseNumber()).append("%'");
        }
        if (Strings.isNotBlank(bean.getProvince())) {
            sb.append(" and province like '%").append(bean.getProvince()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCity())) {
            sb.append(" and city like '%").append(bean.getCity()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCounty())) {
            sb.append(" and county like '%").append(bean.getCounty()).append("%'");
        }
        if (Strings.isNotBlank(bean.getDistrict())) {
            sb.append(" and district like '%").append(bean.getDistrict()).append("%'");
        }
        if (Strings.isNotBlank(bean.getCommunity())) {
            sb.append(" and community like '%").append(bean.getCommunity()).append("%'");
        }
        if (Strings.isNotBlank(queryTime)) {
            String time[] = queryTime.split(",");
            Date date1 = new Date();
            date1.setTime(Long.parseLong(time[0]));
            Date date2 = new Date();
            date2.setTime(Long.parseLong(time[1]));
            SimpleDateFormat SDF_START = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            sb.append(" and update_date > '").append(SDF_START.format(date1)).append("' and update_date < '").append(SDF_START.format(date2)).append("'");
        }
        return sb.toString();
    }

    /**
     * 公共获取pdf和Excel下载列表id
     *
     * @param sqlStr
     * @return
     */
    private List<String> getPdfExcelFileDownSumIdList(String sqlStr) {
        Sql sql = Sqls.create(sqlStr);
        sql.setCallback((Connection con, ResultSet rs, Sql sqlItem) -> {
            List<String> list = Lists.newArrayList();
            while (rs.next()) {
                list.add(rs.getString("id"));
            }
            return list;
        });
        dao().execute(sql);
        return sql.getList(String.class);
    }

    /**
     * 新增人员新信息
     *
     * @param paperVo
     */
    @Override
    @Aop(TransAop.READ_COMMITTED)
    public void insertCenSusPaper(SqfwCenSusPaperVo paperVo) {
        Sqfw_cenSusPaper cenSusPaper1 = new Sqfw_cenSusPaper();
        BeanUtil.copyProperties(paperVo, cenSusPaper1);
        cenSusPaper1.setCreateBy(SecurityUtil.getUserId()).setCreateDate(new Date()).setUpdateDate(new Date());
        this.insert(cenSusPaper1);
    }

    /**
     * 删除
     *
     * @param id
     * @return
     */
    @Override
    @Aop(TransAop.READ_COMMITTED)
    public String deleteById(String id) {
        // 查询出人员信息表记录
        Sqfw_cenSusPaper sqfwCenSusPaper = sqfwCenSusPaperService.fetch(id);
        if (null == sqfwCenSusPaper) {
            return ResultCode.NULL_DATA_ERROR.getMsg();
        }
        // 删除人员信息表记录
        this.delete(id);
        // 更新summary表最新一条记录的a7字段
        if (Strings.isNotBlank(sqfwCenSusPaper.getSqfwSummaryId())) {
            Cnd cnd = Cnd.NEW();
            cnd.and("id", "=", sqfwCenSusPaper.getSqfwSummaryId());
            cnd.orderBy(Sqfw_cenSusPaper::getCreateDate, "DESC");
            cnd.limit(1);
            Sqfw_formSummary sqfwFormSummary = sqfwFormSummaryService.fetch(cnd);
            if (ObjectUtil.isNotEmpty(sqfwFormSummary) && Strings.isNotBlank(sqfwFormSummary.getA7())) {
                List<Sqfw_cenSusPaper> list = Json.fromJsonAsList(Sqfw_cenSusPaper.class, sqfwFormSummary.getA7());
                List<Sqfw_cenSusPaper> listVO = new ArrayList<Sqfw_cenSusPaper>();
                for (Sqfw_cenSusPaper sqfw_cenSusPaper : list) {
                    if (!sqfw_cenSusPaper.getId().equals(id)) {
                        listVO.add(sqfw_cenSusPaper);
                    }
                }
                String listJSONString = JSON.toJSONString(listVO).replaceAll(" ", "");
                sqfwFormSummary.setA7(listJSONString);
                sqfwFormSummaryService.updateIgnoreNull(sqfwFormSummary);
            }
        }
        return ResultCode.SUCCESS.getMsg();
    }

    @Override
    public List<Integer> checkParamFromHouseInfo(List<String> sumIdList, Sqfw_cenSusPaper bean, String queryAll, String queryTime, String queryEntry, String startTime, String endTime, Integer fileIndex) {
        List<Integer> excelNameList = Lists.newArrayList();
        if ("query".equals(queryEntry)) {
            sumIdList = getPdfExcelFileDownSumIdList(bean, queryAll, queryTime);
        }
        if (sumIdList.isEmpty()) return excelNameList;
       /* //zhangp 2023-12-19 根据枚举类型检车文档数据
        List<HouseoldInfoEnum> houseoldInfoEnums = List.of(HouseoldInfoEnum.values());
        for (HouseoldInfoEnum he : houseoldInfoEnums) {
            String peopleInfoStatus = createExcelAndUpload(sumIdList, he.getCode(), "isCheckData", startTime, endTime);
            if (StringUtils.isNotBlank(peopleInfoStatus) && "true".equals(peopleInfoStatus)) {
                excelNameList.add(he.getCode());
            }
        }*/
        //zhangp  根据枚举类型检车文档数据
        List<HouseoldInfoEnum> houseoldInfoEnums = List.of(HouseoldInfoEnum.values());
        if (null == fileIndex) {
            for (HouseoldInfoEnum he : houseoldInfoEnums) {
                //String peopleInfoStatus = createExcelAndUpload(ids, he.getCode(), "isCheckData",startTime,endTime);
                String peopleInfoStatus = generatePeopleInfoExcelForAppByCond(null, null, he.getCode(), "isCheckData", startTime, endTime, sumIdList);
                if (StringUtils.isNotBlank(peopleInfoStatus) && "true".equals(peopleInfoStatus)) {
                    excelNameList.add(he.getCode());
                }
            }
        } else {
            String peopleInfoStatus = generatePeopleInfoExcelForAppByCond(null, null, fileIndex, "isCheckData", startTime, endTime, sumIdList);
            if (StringUtils.isNotBlank(peopleInfoStatus) && "true".equals(peopleInfoStatus)) {
                excelNameList.add(fileIndex);
            }
        }
        return excelNameList;
    }

    @Override
    public String generatePeopleInfoTemplateDownload(Integer fileIndex) {
        if (HouseoldInfoEnum.HOUSEHOLD_PERSONNEL_INFO.getCode().equals(fileIndex)) {
            return createPeopleInfoExcelAndUpload(Lists.newArrayList(), "isTemplate");
        } else if (HouseoldInfoEnum.RETIRE_PERSONNEL.getCode().equals(fileIndex)) {
            return createStateOwnedExcelAndUpload(Lists.newArrayList(), "isTemplate");
        } else if (HouseoldInfoEnum.SIX_KEYNOTE_TABLE_STATIS.getCode().equals(fileIndex)) {
            return houseSurveySixTypePeopleStatisticExport(Lists.newArrayList(), "isTemplate");
        } else if (HouseoldInfoEnum.NINE_TYPES_OF_PERSONNEL_PATROL.getCode().equals(fileIndex)) {
            return nineTypesOfPersonnelPatrolExcel(Lists.newArrayList(), "isTemplate");
        } else if (HouseoldInfoEnum.SIX_CATEGORIES_OF_PERSONNEL.getCode().equals(fileIndex)) {
            return sixCategoriesOfPersonnel(Lists.newArrayList(), "isTemplate");
        }
        return null;
    }

    @Override
    public List<Sqfw_cenSusPaper> getPeopleInfoList(String queryParam, String selectParam, Integer pageSize, Integer pageNo) {
        if (StringUtils.isBlank(queryParam) && StringUtils.isBlank(selectParam)) {
            return Lists.newArrayList();
        }
        Sys_user user = sysUserService.getUserById(SecurityUtil.getUserId());
        if (null == user) {
            return Lists.newArrayList();
        }
        String sqlStr = buildConditionAppPersonInfo(user, queryParam, selectParam, Lists.newArrayList(), pageSize, pageNo);
        Sql sql = Sqls.create(sqlStr);
        sql.setCallback((Connection con, ResultSet rs, Sql sqlItem) -> {
            List<Sqfw_cenSusPaper> list = Lists.newArrayList();
            while (rs.next()) {
                Sqfw_cenSusPaper cenSusPaper = new Sqfw_cenSusPaper();
                cenSusPaper.setId(rs.getString("id"));
                cenSusPaper.setName(rs.getString("name"));
                cenSusPaper.setPhone(rs.getString("phone"));
                String community = rs.getString("community");
                String county = rs.getString("county");
                String name = CommonConstants.DIRECTLY_UNDER.equals(community) || StringUtils.isBlank(community) ? county : community;
                cenSusPaper.setCommunity(name);
                cenSusPaper.setDetailAddress(rs.getString("detail_address"));
                cenSusPaper.setUpdateDate(DateUtil.parse(rs.getString("update_date"), "yyyy-MM-dd HH:mm:ss"));
                cenSusPaper.setSpecialMark(rs.getString("special_mark"));
                cenSusPaper.setSqfwSummaryId(rs.getString("sqfwsummaryId"));
                list.add(cenSusPaper);
            }
            return list;
        });
        dao().execute(sql);
        /*
        List<Sqfw_cenSusPaper> cenSusPaperList = sql.getList(Sqfw_cenSusPaper.class);
        cenSusPaperList = cenSusPaperList.stream().filter(sc -> StringUtils.isNotBlank(sc.getSqfwSummaryId())).collect(Collectors.toList());
        */
        return sql.getList(Sqfw_cenSusPaper.class);
    }

    @Override
    public String generatePeopleInfoExcelForAppByCond(String queryParam, String selectParam, Integer fileIndex, String isCheckData, String startTime, String endTime, List<String> ids) {
        if (HouseoldInfoEnum.SIX_CATEGORIES_OF_PERSONNEL.getCode().equals(fileIndex) || HouseoldInfoEnum.NINE_TYPES_OF_PERSONNEL_PATROL.getCode().equals(fileIndex)) {
            Sys_user user = sysUserService.getUserById(SecurityUtil.getUserId());
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.append(" select a.*,b.fill_date_str,replace(b.a11,'__','_') a11,replace(b.a12,'__','_') a12 from ( ");
            sqlStr.append(buildConditionAppPersonInfo(user, queryParam, selectParam, ids, null, null));
            sqlStr.append("  ) a  ");
            sqlStr.append("  left join");
            sqlStr.append(" (");
            //sqlStr.append(" select  a15, listagg(to_char(fill_date,'yyyy-MM-dd'), ',') within group ( order by fill_date desc) fill_date_str,  listagg(a11, ',') within group ( order by fill_date desc) a11, listagg(a12, ',') within group ( order by fill_date desc) a12 from  shequ.sqfw_form_summary sfs where sfs.type='入户调查' and sfs.a15 is not null and sfs.a15 !=' ' ");
            sqlStr.append(" select  a15,sc.id, listagg(to_char(fill_date,'yyyy-MM-dd'), ',') within group ( order by fill_date asc) fill_date_str,  listagg(case when (a11 is not null and trim(a11) is not null) then a11 else a12 end , '_') within group ( order by fill_date desc) a11, listagg(a12, '_') within group ( order by fill_date desc) a12");
            sqlStr.append(" from  shequ.sqfw_form_summary sfs left join shequ.sqfw_censuspaper sc on sfs.a15=sc.detail_address and sfs.a7 like concat('%',sc.id,'%') ");
            sqlStr.append(" where sfs.type='入户调查' and sfs.a15 is not null and sfs.a15 !=' '");
            if (StringUtils.isNotBlank(startTime)) {
                sqlStr.append(" and sfs.fill_date >='").append(startTime).append("'");
            }
            if (StringUtils.isNotBlank(endTime)) {
                sqlStr.append(" and sfs.fill_date <='").append(endTime).append("'");
            }
            sqlStr.append("   group by sfs.a15,sc.id");
            sqlStr.append(" ) b");
            sqlStr.append(" on b.a15 =a.detail_address and a.id=b.id where 1=1 ");
            if (StringUtils.isNotBlank(startTime)) {
                sqlStr.append(" and a.update_date >='").append(startTime).append("'");
            }
            if (StringUtils.isNotBlank(endTime)) {
                sqlStr.append(" and a.update_date <='").append(endTime).append("'");
            }
            sqlStr.append(" and b.fill_date_str !=' '");
            sqlStr.append(" order by a.update_date desc");
            List<NutMap> nutMapList = sqfwCenSusPaperService.listMap(Sqls.create(sqlStr.toString()));
            List<Sqfw_cenSusPaper> cenSusPaperList = JSONUtil.toList(JSONUtil.toJsonStr(nutMapList), Sqfw_cenSusPaper.class);
            if (cenSusPaperList.isEmpty()) {
                return null;
            }
            //取最新图片后再升序排
            buildUrlParam(cenSusPaperList);
            return createNinAndSixExcel(cenSusPaperList, fileIndex, isCheckData);
        } else {
            if (ids.isEmpty()) {
                List<Sqfw_cenSusPaper> cenSusPaperList = getPeopleInfoList(queryParam, selectParam, null, null);
                if (cenSusPaperList.isEmpty()) {
                    return null;
                }
                ids = cenSusPaperList.stream().map(Sqfw_cenSusPaper::getId).collect(Collectors.toList());

            }
            return createExcelAndUpload(ids, fileIndex, isCheckData, startTime, endTime);
        }
    }

    private void buildUrlParam(List<Sqfw_cenSusPaper> cenSusPaperList) {
        for (Sqfw_cenSusPaper cenSusPaper : cenSusPaperList) {
            String a11 = cenSusPaper.getA11();
            String a12 = cenSusPaper.getA12();
            //巡查图片
            if (StringUtils.isNotBlank(a11)) {
                String[] a11s = a11.split("_");
                List<String> url = new ArrayList<>();
                for (String item : a11s) {
                    if (StringUtils.isNotBlank(item)) {
                        String[] param = item.split(",");
                        //每条记录只取一张
                        url.add(param[0]);
                    }
                }
                Collections.reverse(url);
                cenSusPaper.setA11(String.join(",", url));
            }
            //入户图片
            if (StringUtils.isNotBlank(a12)) {
                String[] a12s = a12.split("_");
                List<String> url = new ArrayList<>();
                for (String item : a12s) {
                    if (StringUtils.isNotBlank(item)) {
                        url.add(item);
                    }

                }
                Collections.reverse(url);
                cenSusPaper.setA12(String.join(",", url));
            }
        }
    }


    @Override
    public List<Integer> checkParamFromHouseInfoForApp(String queryParam, String selectParam, String startTime, String endTime, Integer fileIndex) {
        List<Integer> excelNameList = Lists.newArrayList();
        List<Sqfw_cenSusPaper> cenSusPaperList = getPeopleInfoList(queryParam, selectParam, null, null);
        if (null == cenSusPaperList) {
            return excelNameList;
        }
        List<String> ids = cenSusPaperList.stream().map(Sqfw_cenSusPaper::getId).collect(Collectors.toList());
        //zhangp  根据枚举类型检车文档数据
        List<HouseoldInfoEnum> houseoldInfoEnums = List.of(HouseoldInfoEnum.values());
        if (null == fileIndex) {
            for (HouseoldInfoEnum he : houseoldInfoEnums) {
                //String peopleInfoStatus = createExcelAndUpload(ids, he.getCode(), "isCheckData",startTime,endTime);
                String peopleInfoStatus = generatePeopleInfoExcelForAppByCond(queryParam, selectParam, he.getCode(), "isCheckData", startTime, endTime, ids);
                if (StringUtils.isNotBlank(peopleInfoStatus) && "true".equals(peopleInfoStatus)) {
                    excelNameList.add(he.getCode());
                }
            }
        } else {
            String peopleInfoStatus = generatePeopleInfoExcelForAppByCond(queryParam, selectParam, fileIndex, "isCheckData", startTime, endTime, ids);
            if (StringUtils.isNotBlank(peopleInfoStatus) && "true".equals(peopleInfoStatus)) {
                excelNameList.add(fileIndex);
            }
        }

        return excelNameList;
    }

    @Override
    public List<Map> getHouseSurveyInfoListByAddress(String unitId, String a15) {
        StringBuilder sqlStr = new StringBuilder();
        sqlStr.append(" select a.* from ( ");
        sqlStr.append(" select *,row_number() over(partition by a15 order by create_date desc) as num from shequ.sqfw_form_summary   where type='入户调查'  ");
        sqlStr.append(" and unit_id = '").append(unitId).append("'");
        sqlStr.append(" and a15 like '%").append(a15).append("%'");
        sqlStr.append(" ) a where  a.num=1 and a.a15 is not null and a.a15 !=' '");
        Sql sql = Sqls.create(sqlStr.toString());
        sql.setCallback((Connection con, ResultSet rs, Sql sqlItem) -> {
            List<Map<String, Object>> list = Lists.newArrayList();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("id", rs.getString("id"));
                map.put("a1", rs.getString("a1"));
                if (StringUtils.isNotBlank(rs.getString("a1"))) {
                    map.put("a1Char", PinyinUtil.getFirstLetter(rs.getString("a1"), ""));
                } else {
                    map.put("a1Char", "");
                }
                map.put("a3", rs.getString("a3"));
                map.put("a4", rs.getString("a4"));
                map.put("a5", rs.getString("a5"));
                map.put("a7", rs.getString("a7"));
                map.put("a15", rs.getString("a15"));
                map.put("a16", rs.getString("a16"));
                map.put("a17", rs.getString("a17"));
                map.put("a20", rs.getString("a20"));
                map.put("fillDate", rs.getString("fill_date"));
                map.put("unitId", rs.getString("unit_id"));
                map.put("unitName", rs.getString("unit_name"));
                map.put("a19", rs.getString("a19"));
                map.put("a23", rs.getString("a23"));
                list.add(map);
            }
            return list;
        });
        dao().execute(sql);
        return sql.getList(Map.class);
    }

    private String buildConditionAppPersonInfo(Sys_user user, String queryParam, String selectParam, List<String> ids, Integer pageSize, Integer pageNo) {
        //根据当前登录人五级查询
        String levelParam = user.getProvince() + user.getCity() + user.getDistrict() + user.getCounty();
        if (!CommonConstants.DIRECTLY_UNDER.equals(user.getCommunity())) {
            levelParam = levelParam + user.getCommunity();
        }
        StringBuilder sqlStr = new StringBuilder();
        sqlStr.append(" select  sc.*,sfs.id as sqfwSummaryId  from shequ.sqfw_censuspaper  sc left join shequ.sqfw_form_summary sfs on sfs.id =sc.sqfw_summary_id ");
        if (!ids.isEmpty()) {
            Cnd cnd = Cnd.NEW();
            cnd.and("sc.id", "in", ids);
            sqlStr.append(cnd);
            sqlStr.append(" order by sc.update_date desc");
            return sqlStr.toString();
        }
        sqlStr.append(" where (sc.latest_status is null or sc.latest_status ='') ");
        sqlStr.append(" and sc.attribution like '%").append(levelParam).append("%'");

        sqlStr.append(" and ( ");
        if (StringUtils.isNotBlank(selectParam)) {
            String[] selectParams = selectParam.split(",");
            int count = 0;
            for (String item : selectParams) {
                if (count == 0) {
                    sqlStr.append(" sc.special_mark like '%").append(item).append("%'");
                } else {
                    sqlStr.append(" or sc.special_mark like '%").append(item).append("%'");
                }
                count++;
            }
        } else {
            sqlStr.append(" sc.name like '%").append(queryParam).append("%'");
            sqlStr.append(" or sc.phone like '%").append(queryParam).append("%'");
            sqlStr.append(" or sc.detail_address like '%").append(queryParam).append("%'");
        }
        sqlStr.append(" ) ");
        sqlStr.append(" order by sc.update_date desc ");
        if (null != pageNo) {
            if (null == pageSize) {
                pageSize = 10;
            }
            if (pageNo >= 1) {
                pageNo = (pageNo - 1) * pageSize;
            }
            if (0 != pageSize) {
                sqlStr.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(pageNo);
            }
        }
        return sqlStr.toString();
    }


}
